{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "intro",
   "metadata": {},
   "source": [
    "# 基础数据操作\n",
    "\n",
    "## 核心操作速查\n",
    "\n",
    "| 操作 | 语法 | 说明 |\n",
    "|------|------|------|\n",
    "| 选择列 | `df['col']` 或 `df[['col1', 'col2']]` | 单括号→Series, 双括号→DataFrame |\n",
    "| 筛选行 | `df[条件]` | 使用布尔索引 |\n",
    "| 多条件 | `df[(条件1) & (条件2)]` | &(且), \\|(或), ~(非) |\n",
    "| 排序 | `df.sort_values('col')` | ascending控制升降序 |\n",
    "| 切片 | `df.iloc[0:5]` | 位置索引 |\n",
    "\n",
    "---\n",
    "\n",
    "## 准备工作：生成示例数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "gen-data",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta\n",
    "import random\n",
    "import os\n",
    "\n",
    "np.random.seed(42)\n",
    "\n",
    "# 生成学生数据\n",
    "num_students = 500\n",
    "students = pd.DataFrame({\n",
    "    '学生ID': [f'STU{str(i).zfill(4)}' for i in range(1, num_students + 1)],\n",
    "    '姓名': [f'学生{i}' for i in range(1, num_students + 1)],\n",
    "    '性别': np.random.choice(['男', '女'], size=num_students),\n",
    "    '年级': np.random.choice(['大一', '大二', '大三', '大四'], size=num_students),\n",
    "    '专业': np.random.choice(['计算机', '商务', '市场'], size=num_students),\n",
    "    '入学年份': np.random.choice(range(2020, 2024), size=num_students),\n",
    "    '生源地': np.random.choice(['北京', '上海', '广东', '江苏'], size=num_students)\n",
    "})\n",
    "\n",
    "# 生成订单数据\n",
    "num_orders = 1000\n",
    "orders = pd.DataFrame({\n",
    "    '订单ID': [f'ORD{str(i).zfill(6)}' for i in range(1, num_orders + 1)],\n",
    "    '学生ID': np.random.choice(students['学生ID'], size=num_orders),\n",
    "    '订单日期': [datetime.now() - timedelta(days=random.randint(1, 365)) for _ in range(num_orders)],\n",
    "    '商品类别': np.random.choice(['学习用品', '电子产品', '服装', '食品'], size=num_orders),\n",
    "    '订单金额': np.random.uniform(10, 2000, size=num_orders).round(2),\n",
    "    '支付方式': np.random.choice(['支付宝', '微信支付', '校园卡'], size=num_orders),\n",
    "    '订单状态': np.random.choice(['已完成', '已取消', '处理中'], size=num_orders, p=[0.8, 0.1, 0.1]),\n",
    "    '评分': np.random.choice([1, 2, 3, 4, 5, None], size=num_orders, p=[0.05, 0.1, 0.2, 0.3, 0.3, 0.05])\n",
    "})\n",
    "\n",
    "# 保存数据\n",
    "os.makedirs('../data/demo', exist_ok=True)\n",
    "students.to_csv('../data/demo/students.csv', index=False, encoding='utf-8')\n",
    "orders.to_csv('../data/demo/orders.csv', index=False, encoding='utf-8')\n",
    "\n",
    "print(\"✓ 数据生成完成\")\n",
    "print(f\"学生数据: {students.shape}\")\n",
    "print(f\"订单数据: {orders.shape}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "data-load",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 一、数据选择\n",
    "\n",
    "### Series vs DataFrame\n",
    "\n",
    "| 特征 | Series | DataFrame |\n",
    "|------|--------|--------   ---|\n",
    "| 维度 | 1维 | 2维 |\n",
    "| 选择语法 | `df['col']` | `df[['col1', 'col2']]` |\n",
    "| 返回类型 | 单列数据 | 表格数据 |\n",
    "| 适用场景 | 单变量计算 | 多变量分析 |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "select-data",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 加载数据\n",
    "students = pd.read_csv('../data/demo/students.csv')\n",
    "orders = pd.read_csv('../data/demo/orders.csv')\n",
    "\n",
    "print(\"=== 数据选择 ===\")\n",
    "\n",
    "# 1. 单列选择（Series）\n",
    "names = students['姓名']\n",
    "print(f\"\\n单列选择（返回Series）: {type(names)}\")\n",
    "print(names.head())\n",
    "\n",
    "# 2. 多列选择（DataFrame）\n",
    "student_info = students[['姓名', '专业', '年级']]\n",
    "print(f\"\\n多列选择（返回DataFrame）: {type(student_info)}\")\n",
    "print(student_info.head())\n",
    "\n",
    "# 3. 前N行/后N行\n",
    "print(\"\\n前3行数据:\")\n",
    "print(students.head(3))\n",
    "\n",
    "print(\"\\n后3行数据:\")\n",
    "print(students.tail(3))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "filter",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 二、条件筛选\n",
    "\n",
    "### 比较运算符\n",
    "\n",
    "| 运算符 | 含义 | 示例 |\n",
    "|--------|------|------|\n",
    "| `==` | 等于 | `df['年龄'] == 20` |\n",
    "| `!=` | 不等于 | `df['性别'] != '男'` |\n",
    "| `>` | 大于 | `df['金额'] > 100` |\n",
    "| `<` | 小于 | `df['金额'] < 500` |\n",
    "| `>=` | 大于等于 | `df['评分'] >= 4` |\n",
    "| `<=` | 小于等于 | `df['评分'] <= 3` |\n",
    "\n",
    "### 逻辑运算符\n",
    "\n",
    "| 运算符 | 含义 | 示例 |\n",
    "|--------|------|------|\n",
    "| `&` | 与（且） | `(条件1) & (条件2)` |\n",
    "| `\\|` | 或 | `(条件1) \\| (条件2)` |\n",
    "| `~` | 非（取反） | `~(条件)` |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "filter-data",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 条件筛选 ===\")\n",
    "\n",
    "# 1. 单条件筛选\n",
    "cs_students = students[students['专业'] == '计算机']\n",
    "print(f\"\\n计算机专业学生数量: {len(cs_students)}\")\n",
    "print(cs_students.head())\n",
    "\n",
    "# 2. 多条件筛选（AND）\n",
    "cs_senior = students[\n",
    "    (students['专业'] == '计算机') & \n",
    "    (students['年级'] == '大四')\n",
    "]\n",
    "print(f\"\\n计算机专业大四学生数量: {len(cs_senior)}\")\n",
    "\n",
    "# 3. 多条件筛选（OR）\n",
    "bj_sh = students[\n",
    "    (students['生源地'] == '北京') | \n",
    "    (students['生源地'] == '上海')\n",
    "]\n",
    "print(f\"\\n来自北京或上海的学生数量: {len(bj_sh)}\")\n",
    "\n",
    "# 4. 条件取反\n",
    "not_cs = students[~(students['专业'] == '计算机')]\n",
    "print(f\"\\n非计算机专业学生数量: {len(not_cs)}\")\n",
    "\n",
    "# 5. 数值范围筛选\n",
    "medium_orders = orders[\n",
    "    (orders['订单金额'] >= 100) & \n",
    "    (orders['订单金额'] <= 500)\n",
    "]\n",
    "print(f\"\\n订单金额100-500元的订单数量: {len(medium_orders)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "advanced-filter",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 三、高级筛选方法\n",
    "\n",
    "### 常用方法\n",
    "\n",
    "| 方法 | 功能 | 示例 |\n",
    "|------|------|------|\n",
    "| `isin()` | 值在列表中 | `df['列'].isin(['值1', '值2'])` |\n",
    "| `between()` | 值在范围内 | `df['列'].between(100, 500)` |\n",
    "| `str.contains()` | 字符串包含 | `df['列'].str.contains('关键词')` |\n",
    "| `str.startswith()` | 字符串开头 | `df['列'].str.startswith('前缀')` |\n",
    "| `isna()` | 是否为空 | `df['列'].isna()` |\n",
    "| `notna()` | 是否非空 | `df['列'].notna()` |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "advanced-filter-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 高级筛选 ===\")\n",
    "\n",
    "# 1. isin() - 值在列表中\n",
    "major_cities = ['北京', '上海', '广东']\n",
    "city_students = students[students['生源地'].isin(major_cities)]\n",
    "print(f\"\\n来自一线城市的学生数量: {len(city_students)}\")\n",
    "\n",
    "# 2. between() - 值在范围内\n",
    "medium_orders = orders[orders['订单金额'].between(100, 500)]\n",
    "print(f\"订单金额在100-500的订单数量: {len(medium_orders)}\")\n",
    "\n",
    "# 3. str.contains() - 字符串包含\n",
    "id_with_00 = students[students['学生ID'].str.contains('00')]\n",
    "print(f\"ID中包含'00'的学生数量: {len(id_with_00)}\")\n",
    "\n",
    "# 4. str.startswith() - 字符串开头\n",
    "stu_0 = students[students['学生ID'].str.startswith('STU0')]\n",
    "print(f\"ID以'STU0'开头的学生数量: {len(stu_0)}\")\n",
    "\n",
    "# 5. 检查空值\n",
    "rated_orders = orders[orders['评分'].notna()]\n",
    "print(f\"\\n已评分的订单数量: {len(rated_orders)}\")\n",
    "print(f\"未评分的订单数量: {len(orders) - len(rated_orders)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sorting",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 四、数据排序\n",
    "\n",
    "### sort_values() 参数\n",
    "\n",
    "| 参数 | 说明 | 示例 |\n",
    "|------|------|------|\n",
    "| `by` | 排序列 | `by='金额'` |\n",
    "| `ascending` | 升序(True)/降序(False) | `ascending=False` |\n",
    "| `na_position` | 空值位置(first/last) | `na_position='last'` |\n",
    "| `inplace` | 是否原地修改 | `inplace=True` |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sorting-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 数据排序 ===\")\n",
    "\n",
    "# 1. 单列排序（升序）\n",
    "sorted_asc = orders.sort_values('订单金额')\n",
    "print(\"\\n订单金额最低的5个订单:\")\n",
    "print(sorted_asc[['订单ID', '订单金额']].head())\n",
    "\n",
    "# 2. 单列排序（降序）\n",
    "sorted_desc = orders.sort_values('订单金额', ascending=False)\n",
    "print(\"\\n订单金额最高的5个订单:\")\n",
    "print(sorted_desc[['订单ID', '订单金额']].head())\n",
    "\n",
    "# 3. 多列排序\n",
    "sorted_multi = students.sort_values(['专业', '年级'])\n",
    "print(\"\\n按专业和年级排序:\")\n",
    "print(sorted_multi[['专业', '年级', '姓名']].head(10))\n",
    "\n",
    "# 4. 自定义排序顺序\n",
    "grade_order = {'大一': 1, '大二': 2, '大三': 3, '大四': 4}\n",
    "sorted_custom = students.sort_values(\n",
    "    '年级',\n",
    "    key=lambda x: x.map(grade_order)\n",
    ")\n",
    "print(\"\\n按年级顺序排序:\")\n",
    "print(sorted_custom[['年级', '姓名']].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "applications",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 五、实战应用\n",
    "\n",
    "### 综合案例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "applications-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 实战案例 ===\")\n",
    "\n",
    "# 案例1: 查找高消费学生\n",
    "high_value_orders = orders[orders['订单金额'] > 1000]\n",
    "high_value_students = high_value_orders['学生ID'].unique()\n",
    "high_spenders = students[students['学生ID'].isin(high_value_students)]\n",
    "print(\"\\n1. 有高额订单(>1000元)的学生数量:\", len(high_spenders))\n",
    "print(high_spenders[['姓名', '专业', '年级']].head())\n",
    "\n",
    "# 案例2: 分析不同专业的订单情况\n",
    "# 合并数据\n",
    "merged = orders.merge(students[['学生ID', '专业']], on='学生ID')\n",
    "major_stats = merged.groupby('专业').agg({\n",
    "    '订单ID': 'count',\n",
    "    '订单金额': ['sum', 'mean']\n",
    "})\n",
    "major_stats.columns = ['订单数量', '总金额', '平均金额']\n",
    "print(\"\\n2. 各专业订单统计:\")\n",
    "print(major_stats.round(2))\n",
    "\n",
    "# 案例3: 查找热门商品类别\n",
    "category_stats = orders.groupby('商品类别').agg({\n",
    "    '订单ID': 'count',\n",
    "    '订单金额': 'sum'\n",
    "}).sort_values('订单ID', ascending=False)\n",
    "category_stats.columns = ['订单数量', '总销售额']\n",
    "print(\"\\n3. 商品类别销售排名:\")\n",
    "print(category_stats.round(2))\n",
    "\n",
    "# 案例4: 查找已完成且高评分的订单\n",
    "good_orders = orders[\n",
    "    (orders['订单状态'] == '已完成') & \n",
    "    (orders['评分'] >= 4)\n",
    "]\n",
    "print(f\"\\n4. 已完成且评分≥4的优质订单数量: {len(good_orders)}\")\n",
    "print(f\"   优质订单比例: {len(good_orders) / len(orders) * 100:.2f}%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "summary",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 操作速查表\n",
    "\n",
    "### 完整语法\n",
    "\n",
    "```python\n",
    "# 1. 数据选择\n",
    "df['col']                    # 单列（Series）\n",
    "df[['col1', 'col2']]        # 多列（DataFrame）\n",
    "df.head(n)                   # 前n行\n",
    "df.tail(n)                   # 后n行\n",
    "\n",
    "# 2. 条件筛选\n",
    "df[df['col'] == value]       # 等于\n",
    "df[df['col'] > value]        # 大于\n",
    "df[(条件1) & (条件2)]         # 多条件AND\n",
    "df[(条件1) | (条件2)]         # 多条件OR\n",
    "df[~条件]                     # 条件取反\n",
    "\n",
    "# 3. 高级筛选\n",
    "df[df['col'].isin([v1, v2])]           # 值在列表中\n",
    "df[df['col'].between(a, b)]            # 值在范围内\n",
    "df[df['col'].str.contains('text')]     # 包含文本\n",
    "df[df['col'].notna()]                  # 非空值\n",
    "\n",
    "# 4. 排序\n",
    "df.sort_values('col')                  # 单列升序\n",
    "df.sort_values('col', ascending=False) # 单列降序\n",
    "df.sort_values(['col1', 'col2'])      # 多列排序\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "## 小结\n",
    "\n",
    "**核心技能**:\n",
    "1. **数据选择**: 理解Series和DataFrame的区别\n",
    "2. **条件筛选**: 掌握比较运算符和逻辑运算符\n",
    "3. **高级筛选**: 使用isin、between、str方法\n",
    "4. **数据排序**: 单列/多列排序,自定义排序\n",
    "5. **综合应用**: 组合多个操作完成复杂查询\n",
    "\n",
    "**注意事项**:\n",
    "- 多条件筛选时,每个条件都要用括号括起来\n",
    "- 注意区分`&`(且)和`|`(或),不要使用`and`/`or`\n",
    "- 排序不会修改原DataFrame,除非设置`inplace=True`\n",
    "\n",
    "**下一步**: 学习数据计算与转换"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:base] *",
   "language": "python",
   "name": "conda-base-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
